package ru.vt.simpleservlet.dbinterraction;

import ru.vt.simpleservlet.data.AllData;
import ru.vt.simpleservlet.data.DataPosition;

import java.sql.*;
import java.util.List;

/**
 * Оболочка к БД
 *
 * @author Vit
 */
public class Extractor {
    /**
     * Достает из БД перечень всех индивидуальных номеров и передает их List
     *
     * @param versionList КОллекция, в которую передаются индивидуальные номера
     */
    public static void extractVersions(List<String> versionList) {
        try {
            Class.forName(Settings.getDriverName());
            String url = "jdbc:mysql://" + Settings.getServerName() + ":" + Settings.getServerPort() + "/" + Settings.getMydatabase();
            Connection connection = (Connection) DriverManager.getConnection(url, Settings.getUsername(), Settings.getPassword());
            Statement statement = (Statement) connection.createStatement();
            String query = "SELECT versioncode FROM restorant.positionlist";
            ResultSet resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                versionList.add(resultSet.getString(1));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Достает из БД все данные элементов
     *
     * @param allData Объект в который передаются все данные элементов
     */
    public static void extractData(AllData allData) {
        try {
            Class.forName(Settings.getDriverName());
            String url = "jdbc:mysql://" + Settings.getServerName() + ":" + Settings.getServerPort() + "/" + Settings.getMydatabase();
            Connection connection = (Connection) DriverManager.getConnection(url, Settings.getUsername(), Settings.getPassword());
            Statement statement = (Statement) connection.createStatement();
            String query = "SELECT * FROM restorant.positionlist";
            ResultSet resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                allData.Add(new DataPosition(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), Float.valueOf(resultSet.getString(5).trim()).floatValue(), getImagePath() + resultSet.getString(6)));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Возвращает базовый URL к картинке, за вычетом названия
     */
    public static String getImagePath() {
        try {
            Class.forName(Settings.getDriverName());
            String url = "jdbc:mysql://" + Settings.getServerName() + ":" + Settings.getServerPort() + "/" + Settings.getMydatabase();
            Connection connection = (Connection) DriverManager.getConnection(url, Settings.getUsername(), Settings.getPassword());
            Statement statement = (Statement) connection.createStatement();
            String query = "SELECT valuesetting FROM restorant.settings WHERE keysetting='imagepath';";
            ResultSet resultSet = statement.executeQuery(query);
            String imagepath = "";
            while (resultSet.next()) {
                return resultSet.getString(1);
            }
            return imagepath;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * Возвращает путь к локальной папке с изображениями
     */
    public static String getImageFolder() {
        try {
            Class.forName(Settings.getDriverName());
            String url = "jdbc:mysql://" + Settings.getServerName() + ":" + Settings.getServerPort() + "/" + Settings.getMydatabase();
            Connection connection = (Connection) DriverManager.getConnection(url, Settings.getUsername(), Settings.getPassword());
            Statement statement = (Statement) connection.createStatement();
            String query = "SELECT valuesetting FROM restorant.settings WHERE keysetting='imagefolder';";
            ResultSet resultSet = statement.executeQuery(query);
            String imagepath = "";
            while (resultSet.next()) {
                imagepath = resultSet.getString(1);
            }
            return imagepath;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * Возвращает путь к локальной папке с временными изображениями
     */
    public static String getImageTmpFolder() {
        try {
            Class.forName(Settings.getDriverName());
            String url = "jdbc:mysql://" + Settings.getServerName() + ":" + Settings.getServerPort() + "/" + Settings.getMydatabase();
            Connection connection = (Connection) DriverManager.getConnection(url, Settings.getUsername(), Settings.getPassword());
            Statement statement = (Statement) connection.createStatement();
            String query = "SELECT valuesetting FROM restorant.settings WHERE keysetting='imagetempfolder';";
            ResultSet resultSet = statement.executeQuery(query);
            String imagepath = "";
            while (resultSet.next()) {
                imagepath = resultSet.getString(1);
            }
            return imagepath;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
